const mysql = require('mysql2/promise');
require('dotenv').config();

// 数据库连接池配置
const pool = mysql.createPool({
  host: process.env.DB_HOST || 'localhost',
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || '',
  database: process.env.DB_NAME || 'yx-design',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
  enableKeepAlive: true,
  keepAliveInitialDelay: 0
});

// 测试数据库连接
const testConnection = async () => {
  let retries = 5;
  while (retries > 0) {
    try {
      const connection = await pool.getConnection();
      console.log('✅ 数据库连接成功');
      connection.release();
      return true;
    } catch (error) {
      console.error(`❌ 数据库连接失败 (剩余重试次数: ${retries - 1}):`, error.message);
      retries--;
      if (retries === 0) {
        console.error('❌ 数据库连接失败，请检查配置');
        return false;
      }
      // 等待3秒后重试
      await new Promise(resolve => setTimeout(resolve, 3000));
    }
  }
  return false;
};

// 初始化数据库表
const initTables = async () => {
  try {
    const connection = await pool.getConnection();
    
    // 创建素材分类表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS material_types (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL COMMENT '分类名称',
        description TEXT COMMENT '分类描述',
        sort_order INT DEFAULT 0 COMMENT '排序',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='素材分类表'
    `);
    
    // 创建素材表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS materials (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(200) NOT NULL COMMENT '素材名称',
        type_id INT COMMENT '分类ID',
        url VARCHAR(500) NOT NULL COMMENT '素材URL',
        thumbnail VARCHAR(500) COMMENT '缩略图URL',
        width INT COMMENT '宽度',
        height INT COMMENT '高度',
        file_size INT COMMENT '文件大小(字节)',
        file_type VARCHAR(50) COMMENT '文件类型',
        tags TEXT COMMENT '标签(JSON格式)',
        is_public BOOLEAN DEFAULT TRUE COMMENT '是否公开',
        download_count INT DEFAULT 0 COMMENT '下载次数',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (type_id) REFERENCES material_types(id) ON DELETE SET NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='素材表'
    `);
    
    // 创建用户素材表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS user_materials (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id VARCHAR(100) COMMENT '用户ID',
        name VARCHAR(200) NOT NULL COMMENT '素材名称',
        type_id INT COMMENT '分类ID',
        url VARCHAR(500) NOT NULL COMMENT '素材URL',
        thumbnail VARCHAR(500) COMMENT '缩略图URL',
        width INT COMMENT '宽度',
        height INT COMMENT '高度',
        file_size INT COMMENT '文件大小(字节)',
        file_type VARCHAR(50) COMMENT '文件类型',
        tags TEXT COMMENT '标签(JSON格式)',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (type_id) REFERENCES material_types(id) ON DELETE SET NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户素材表'
    `);
    
    console.log('✅ 数据库表初始化完成');
    connection.release();
  } catch (error) {
    console.error('❌ 数据库表初始化失败:', error.message);
  }
};

// 启动时测试连接并初始化表
testConnection().then(success => {
  if (success) {
    initTables();
  }
});

module.exports = pool;